package com.luobo.pcsdepositmanage.controller;

import com.alibaba.excel.metadata.CellExtra;
import com.luobo.pcsdepositmanage.pojo.createacsfile.AcsFile;
import com.luobo.pcsdepositmanage.pojo.createacsfile.AcsInOutDescribe;
import com.luobo.pcsdepositmanage.pojo.createacsfile.AcsInOutDescribeResult;
import com.luobo.pcsdepositmanage.util.EasyExcelUtils;
import com.luobo.pcsdepositmanage.util.ExcelOperationUtil;
import org.apache.poi.common.usermodel.HyperlinkType;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCreationHelper;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.io.File;
import java.io.IOException;
import java.util.*;

@RestController
public class createExcelController {

    private static final Logger LOGGER = LoggerFactory.getLogger(createExcelController.class);

    //存储文件：esb模板.xlsx，sheet页：系统级字典项
    private List<Map<Integer,Object>> sysDictmapList;
    //存储文件：acsFileList.xlsx，sheet页：是否生成
    private List<Map<Integer,Object>> acsFileList;
    //转换ACSFileList.xlsx文件为map存储，服务编码作为key值
    /*结构举例
    {
    "A0611995304"：{
    "acsIsCreate":"是",
    "acsDepositCate":"结算类",
    "acsDepositIdenty":"账务类",
    "acsDepositSyscode":"自助银行|POS系统",
    }
    }
     */
    private Map<String,Object> acsNameMap = new HashMap<>();

    private Workbook workbook;

    @Value("${filedir.createfile}")
    private String filedir;

    @Value("${filedir.resultfilename}")
    private String resultFileName;

    @Value("${createexcle.clonenum}")
    private int cloneNum ;

    @Value("${createexcle.dirnum}")
    private int dirNum ;

    /**
     * 启动springboot服务后，通过http://localhost:8899/createfile 地址调用
     * @return
     */
    @RequestMapping("/createfile")
    public String getReadFiledir(){
        Map<String,Object> paraMap = new HashMap<>();
        try {
            LOGGER.info("开始处理----createfile");
//            this.filedir = "/Users/liuminghao/Documents/tmp";
            String filename = "系统级字典项.xlsx";
            String sheetname = "系统级数据字典";

            int headrownum = 3;
            paraMap.put("filedir",this.filedir);
            paraMap.put("filename",filename);
            paraMap.put("sheetname",sheetname);
            paraMap.put("headrownum",headrownum);

            LOGGER.info("开始读入文件:{},读入sheet页:{}",filename,sheetname);

//获取数据字典
//            通过easyExcel读取数据
//            this.sysDictmapList = EasyExcelUtils.noModelReadEasyExcel(paraMap);
//            通过POI读取数据
            this.sysDictmapList = ExcelOperationUtil.readExcelFromFileForMapToListMap(paraMap);

            filename = "esb模板.xlsx";
            paraMap.put("filename",filename);

            this.workbook = ExcelOperationUtil.getWorkbookFromExcel(filedir+File.separator+filename);


            filename = "acsFileList.xlsx";
            sheetname = "是否生成";
            headrownum = 1;
            paraMap.put("filename",filename);
            paraMap.put("sheetname",sheetname);
            paraMap.put("headrownum",headrownum);
//            通过easyExcel读取数据
//            this.acsFileList = EasyExcelUtils.noModelReadEasyExcel(paraMap);
//            通过POI读取数据
            this.acsFileList = ExcelOperationUtil.readExcelFromFileForMapToListMap(paraMap);
//            处理acsFileList数据，转换成Map<String,Object>格式数据
            this.acsNameMap = convertMapFromAcsFileList();

        } catch (Exception e) {
            e.printStackTrace();
            LOGGER.info(e.getStackTrace().toString());
        }

//        String filedir = "/Users/liuminghao/Documents/tmp/ACSFile";
//        设置操作目录
        File file = new File(this.filedir+File.separator+"ACSFile");
        fileOperation(file);
        writeExcel();

        LOGGER.info("处理完成----createfile");

        return "Success";
    }

    public Map<String ,Object> convertMapFromAcsFileList(){
        Map<String,Object> resultMap = new HashMap<>();


//            处理acsFileList数据，转换成Map<String,Object>格式数据
        for (int i = 0; i < this.acsFileList.size(); i++) {
            Object tempObject = this.acsFileList.get(i).get(0);
            Object tempObject2 = this.acsFileList.get(i).get(1);
            Object tempObject3 = this.acsFileList.get(i).get(2);
            Object tempObject4 = this.acsFileList.get(i).get(3);
            Object tempObject5 = this.acsFileList.get(i).get(4);
            Map<String,String> tempMap = new HashMap<>();

            if(tempObject2 != null){
                tempMap.put("acsIsCreate",(String)tempObject2);
            }else{
                tempMap.put("acsIsCreate","否");
            }
            if(tempObject3 != null){
                tempMap.put("acsDepositCate",(String)tempObject3);
            }else{
                tempMap.put("acsDepositCate","");
            }
            if(tempObject4 != null){
                tempMap.put("acsDepositIdenty",(String)tempObject4);
            }else{
                tempMap.put("acsDepositIdenty","");
            }
            if(tempObject5 != null){
                tempMap.put("acsDepositSyscode",(String)tempObject5);
            }else{
                tempMap.put("acsDepositSyscode","");
            }
            if(tempObject != null && tempObject.toString().length()>0){
                resultMap.put((String)tempObject,tempMap);
            }
        }

        return resultMap;
    }

    public boolean isCreateAcs(File file){
        boolean isCreateFlag = false;

        String tempFileName = file.getName();
        String[] tempStringArr = tempFileName.split("_");
        if(tempStringArr.length>3){
            String acsName = tempStringArr[3];
            if(this.acsNameMap.containsKey(acsName)){
                Map<String,String> tempString = (Map<String, String>) this.acsNameMap.get(acsName);
                if(tempString.get("acsIsCreate").equals("是")){
                    isCreateFlag = true;
                }
            }
        }


        return isCreateFlag;

    }

    public void fileOperation(File file) {
        try {
            if (file.isDirectory()) {
                File[] files = file.listFiles();
                for (File f : files) {
                    fileOperation(f);
                }
            } else {
                if(file.getName()!=null && file.getName().length()>0){

                    int lastIndexOf = file.getName().lastIndexOf(".");
                    //获取文件的后缀名 .jpg
                    String suffix = file.getName().substring(lastIndexOf);
//                    System.out.println(suffix);
                    if(suffix.equals(".xlsx")){
                        LOGGER.info("开始读入文件:{}",file.getName());
                        if(isCreateAcs(file)){
                            operaAcsFile(file);
                        }else {
                            LOGGER.info("文件：{}，不再生成列表acsFileList.xlsx中",file.getName());
                        }
                    }else{
                        LOGGER.info("文件:{},不是xlsx格式文件",file.getName());
                    }
                }

            }
        } catch (Exception e) {
            e.printStackTrace();
            LOGGER.info(e.getStackTrace().toString());
        }
    }


    public void operaAcsFile(File file){
        Map<String,Object> paraMap = new HashMap<>();
//        配置读取excel参数
        String filedir = null;
        filedir = file.getParentFile().getPath();
        String filename = file.getName();
        paraMap.put("filedir", filedir);
        paraMap.put("filename", filename);

        Workbook tempworkbook = null;
        try {
            tempworkbook = ExcelOperationUtil.getWorkbookFromExcel(file.getAbsolutePath());
        } catch (IOException e) {
            e.printStackTrace();
        }

        List<Map<Integer, Object>> acsmapList = null;
        List<Integer> areaIntList = new ArrayList<>();
        List<Map<Integer, Object>> acsmapListQT = null;
        Iterator<Sheet> sheetIterator = tempworkbook.sheetIterator();
        while (sheetIterator.hasNext()) {
            Sheet sheet = sheetIterator.next();
            String sheetname = sheet.getSheetName();

            if(sheetname.trim().equals("ACS")){
                int headrownum = 0;

                paraMap.put("sheetname", sheetname);
                paraMap.put("headrownum", headrownum);

                LOGGER.info("设置读取文件目录:{}", paraMap.get("filedir"));
                LOGGER.info("设置读取文件名称:{}", paraMap.get("filename"));
                LOGGER.info("设置读取文件sheet页:{}", paraMap.get("sheetname"));
                LOGGER.info("设置读取文件起始行号:{}", paraMap.get("headrownum"));
//        System.out.println(paraMap);
//获取ACS定义文件数据
                try {
//                    acsmapList = EasyExcelUtils.noModelReadEasyExcel(paraMap);
                    acsmapList = ExcelOperationUtil.readExcelFromFileForMapToListMap(paraMap);
                    LOGGER.info("读取Sheet页:{},获取到数据:{}行", paraMap.get("sheetname"), acsmapList.size());

                } catch (Exception e) {
                    e.printStackTrace();
                    LOGGER.info(e.getStackTrace().toString());

                }
//读取区域范围
                for (int i = 0; i < acsmapList.size(); i++) {
                    if (acsmapList.get(i).get(0) != null && acsmapList.get(i).get(0).toString().length()>0) {
//                System.out.println(acsmapList.get(i).get(0));
                        areaIntList.add(i);
                    }
                }
//        System.out.println(areaIntList);
            }else if (sheetname.trim().equals("ACS-其他属性")){
//                sheetname = "ACS-其他属性";
                int pageNum = tempworkbook.getSheetIndex(sheet);
                paraMap.put("sheetname", pageNum);
                LOGGER.info("设置读取文件sheet页:{}", paraMap.get("sheetname"));
//获取ACS定义文件ACS-其他数据
                try {
//                    acsmapListQT = EasyExcelUtils.noModelReadEasyExcel(paraMap);
                    acsmapListQT = ExcelOperationUtil.readExcelFromFileForMapToListMap(paraMap);
                    LOGGER.info("读取Sheet页:{},获取到数据:{}行", paraMap.get("sheetname"), acsmapListQT.size());
                } catch (Exception e) {
                    e.printStackTrace();
                    LOGGER.info(e.getStackTrace().toString());
                }
            }



//
//        addExcel(acsmapList,areaIntList,acsmapListQT);
//        writeExcel();

//
        }

        AcsFile acsFile = convertAcsFileFromList(acsmapList, areaIntList, acsmapListQT);
        acsFile.setAcsFileDir(this.filedir);
        acsFile.setAcsFileName(filename);

        addExcelForAcsFile(acsFile);


//
    }

    public AcsFile convertAcsFileFromList(List<Map<Integer,Object>> acsmapList,List<Integer> integerList,List<Map<Integer,Object>> acsmapListQT){
        AcsFile acsFile = new AcsFile();
//        List<String> systemList = getAreaList(acsmapList,integerList,3,4,5);

        Object tempString = acsmapList.get(0).get(5);
        if(tempString != null && tempString.toString().length()>0){
            acsFile.setAcsDepositName((String) tempString);
//            LOGGER.info("当前处理PCS服务名称:{}",acsFile.getAcsDepositName());

        }else{
            acsFile.setAcsDepositName("未正确获取PCS服务名称");
            LOGGER.info("获取ACS服务名称错误");
        }

        tempString = null;
        tempString = acsmapList.get(0).get(3);
        if(tempString != null && tempString.toString().length()>0){
            acsFile.setAcsDepositCode((String) tempString);
            LOGGER.info("当前处理PCS服务编码:{}:当前处理PCS服务名称:{}",acsFile.getAcsDepositCode(),acsFile.getAcsDepositName());

        }else{
            acsFile.setAcsDepositCode("未正确获取PCS服务编码");
            LOGGER.info("获取ACS服务名称错误");
        }

        Map<String,String> tempMap = null;

        if(acsNameMap.containsKey(acsFile.getAcsDepositCode())){
            tempMap = (Map<String, String>) acsNameMap.get(acsFile.getAcsDepositCode());
            acsFile.setAcsDepositCate(tempMap.get("acsDepositCate"));
            acsFile.setAcsDepositIdenty(tempMap.get("acsDepositIdenty"));
            acsFile.setAcsDepositSyscode(tempMap.get("acsDepositSyscode"));
        }else {
            acsFile.setAcsDepositCate("");
            acsFile.setAcsDepositIdenty("");
        }

        tempString = null;
        tempString = acsmapList.get(1).get(1);
        if(tempString != null && tempString.toString().length()>0){
            acsFile.setAcsDepositDescribe((String)tempString);
        }else{
            acsFile.setAcsDepositDescribe("未正确获取PCS服务功能描述");
            LOGGER.info("未正确获取PCS服务功能描述");
        }

        tempString = null;
        tempString = acsmapList.get(3).get(1);
        if(tempString != null && tempString.toString().length()>0){
            acsFile.setAcsDepositText((String)tempString);
        }else{
            acsFile.setAcsDepositText("未正确获取PCS服务功能内容");
            LOGGER.info("未正确获取PCS服务功能内容");
        }

        if(acsFile.getAcsDepositSyscode() == null || acsFile.getAcsDepositSyscode().length() == 0){
            List<String> systemList = getAreaList(acsmapList,integerList,3,4,5);
            if(systemList!=null && systemList.size()>0){
                acsFile.setAcsDepositSyscode(String.join("|",systemList));
            }else{
                LOGGER.info("获取渠道列表为空");
                acsFile.setAcsDepositSyscode("渠道列表为空");
            }
        }

        tempString = null;
        if(acsmapListQT != null && acsmapListQT.size()>2 && acsmapListQT.get(2).size()>5){
            tempString = acsmapListQT.get(2).get(5);
        }
        if(tempString != null && tempString.toString().length()>0){
            acsFile.setAcsDepositSAF((String)tempString);
        }else{
            acsFile.setAcsDepositSAF("未正确获取PCS服务SAF属性内容");
            LOGGER.info("未正确获取PCS服务SAF属性内容");
        }

        acsFile.convertAcsInOutDescribeFromList(acsmapList,integerList,4,5,"in");
        acsFile.convertInToResult(this.sysDictmapList);

        acsFile.convertAcsInOutDescribeFromList(acsmapList,integerList,5,6,"out");
        acsFile.convertOutToResult(this.sysDictmapList);

        return acsFile;
    }


    public void addExcelForAcsFile(AcsFile acsFile){

        Sheet operaSheet = this.workbook.getSheetAt(this.dirNum);
        int physicalNumberOfRows = 1;
        Row operaRow = null;

        for (int i = 1; i < operaSheet.getPhysicalNumberOfRows(); i++) {
            operaRow = operaSheet.getRow(i);
            if(operaRow == null ||
                    operaRow.getCell(0) == null ||
                    ExcelOperationUtil.getValue(operaRow.getCell(0)) == null ||
                    ExcelOperationUtil.getValue(operaRow.getCell(0)).equals("")){
                physicalNumberOfRows = i;
                break;
            }
        }


        if(operaRow == null){
            operaRow = operaSheet.createRow(physicalNumberOfRows);
        }

        Cell operaCell = null;
//        设置序号
        ExcelOperationUtil.setCellForIndex(operaRow,0,physicalNumberOfRows);
//        设置服务名称
        ExcelOperationUtil.setCellForIndex(operaRow,1,acsFile.getAcsDepositName());
//        设置提供方组件名称
        ExcelOperationUtil.setCellForIndex(operaRow,2,"个人存款组件");
//        设置业务类别
        ExcelOperationUtil.setCellForIndex(operaRow,3,acsFile.getAcsDepositCate());
//        设置服务性质
        ExcelOperationUtil.setCellForIndex(operaRow,4,acsFile.getAcsDepositIdenty());

//        设置服务消费方系统
        ExcelOperationUtil.setCellForIndex(operaRow,7,acsFile.getAcsDepositSyscode());

//        设置超链接及提供方服务标识
        CreationHelper createHelper = new XSSFCreationHelper((XSSFWorkbook) workbook);
        Hyperlink  link1 = createHelper.createHyperlink(HyperlinkType.DOCUMENT);

        link1.setAddress("#"+acsFile.getAcsDepositCode()+"!A1");
        operaCell = operaRow.getCell(6);
        if (operaCell == null){
            operaCell = operaRow.createCell(6);
        }
        operaCell.setHyperlink(link1);
        operaCell.setCellValue(acsFile.getAcsDepositCode());

        operaSheet = this.workbook.cloneSheet(this.cloneNum);
        int operaInt = this.workbook.getSheetIndex(operaSheet);
        String sheetname = acsFile.getAcsDepositCode();
        this.workbook.setSheetName(operaInt,sheetname);
//        List<CellRangeAddress> originMerged = operaSheet.getMergedRegions();



        ExcelOperationUtil.setCellForCoordinate(operaSheet,1,0,acsFile.getAcsDepositName());
        ExcelOperationUtil.setCellForCoordinate(operaSheet,5,3,acsFile.getAcsDepositDescribe());
        ExcelOperationUtil.setCellForCoordinate(operaSheet,6,3,"个人存款组件");
        ExcelOperationUtil.setCellForCoordinate(operaSheet,18,3,acsFile.getAcsDepositSAF());


        ExcelOperationUtil.setCellForCoordinate(operaSheet,19,3,acsFile.getAcsDepositSyscode());
        ExcelOperationUtil.setCellForCoordinate(operaSheet,20,3,"否");


        int l1 = intOutAreaWriteForAcsFile(operaSheet,29,acsFile.getAcsInDescribeResultList().size(),0,acsFile.getAcsInDescribeResultList());
        int l2 = intOutAreaWriteForAcsFile(operaSheet,43,acsFile.getAcsOutDescribeResultList().size(),l1,acsFile.getAcsOutDescribeResultList());

    }


    public int intOutAreaWriteForAcsFile(Sheet operaSheet, int flagNum , int overNum, int anyNum, List<AcsInOutDescribeResult> acsInOutDescribeResults){
        Row operaRow = null;
        Cell operaCell = null;
        int l1 = 0;
//        flagNum = 29|43
        if(overNum>9){
            operaSheet.shiftRows( flagNum+anyNum+9, operaSheet.getLastRowNum()+1, overNum-9, true, false);
        }

        for (int i = flagNum+anyNum; i <flagNum+anyNum+overNum; i++) {
            if(i > flagNum+8+anyNum){

//                operaSheet.shiftRows( i, operaSheet.getLastRowNum()+1, 1, true, false);
                l1++;

                operaRow = operaSheet.createRow(i);
                operaRow.setRowStyle(operaSheet.getRow(30).getRowStyle());
            }else {
                operaRow = operaSheet.getRow(i);
            }

            operaRow.setHeight(operaSheet.getRow(i-1).getHeight());
            for (int j = 1; j < 10; j++) {
                operaCell = operaRow.getCell(j);
                if(operaCell == null){
                    operaCell = operaRow.createCell(j);
                    operaCell.setCellStyle(operaSheet.getRow(i-1).getCell(j).getCellStyle());
//                    System.out.println(operaCell.getStringCellValue());
                }
                AcsInOutDescribeResult acsInOutDescribeResult = acsInOutDescribeResults.get(i-flagNum-anyNum);
                String tempString = null;
                switch (j){
                    case 1:
                        tempString = acsInOutDescribeResult.getOrderNum();
                        break;
                    case 2:
                        tempString = acsInOutDescribeResult.getEnName();
                        break;
                    case 3:
                        tempString = acsInOutDescribeResult.getChName();
                        break;
                    case 4:
                        tempString = acsInOutDescribeResult.getDataDictCode();
                        break;
                    case 5:
                        tempString = acsInOutDescribeResult.getDataDictTypeDescribe();
                        break;
                    case 6:
                        tempString = acsInOutDescribeResult.getDataType();
                        break;
                    case 7:
                        tempString = acsInOutDescribeResult.getIsRequired();
                        break;
                    case 8:
                        tempString = acsInOutDescribeResult.getAttributeDescribe();
                        break;
                    case 9:
                        tempString = acsInOutDescribeResult.getRemarkDescribe();
                        break;
                }
                if(tempString != null){
                    operaCell.setCellValue(tempString);
                }else{
                    continue;
                }
            }

        }
        return  l1 ;

    }


//    -------------------------------------------->

    public void addExcel(List<Map<Integer,Object>> acsmapList,List<Integer> integerList,List<Map<Integer,Object>> acsmapListQT){
        List<String> systemList = getAreaList(acsmapList,integerList,3,4,5);

        Sheet operaSheet = this.workbook.getSheetAt(6);
        int physicalNumberOfRows = 1;
        Row operaRow = null;

        for (int i = 1; i < operaSheet.getPhysicalNumberOfRows(); i++) {
            operaRow = operaSheet.getRow(i);
            if(operaRow == null ||
                    operaRow.getCell(0) == null ||
                    ExcelOperationUtil.getValue(operaRow.getCell(0)) == null ||
                    ExcelOperationUtil.getValue(operaRow.getCell(0)).equals("")){
                physicalNumberOfRows = i;
                break;
            }
        }

        if(operaRow == null){
            operaRow = operaSheet.createRow(physicalNumberOfRows);
        }

        Cell operaCell = null;
//        设置序号
        ExcelOperationUtil.setCellForIndex(operaRow,0,physicalNumberOfRows);
//        设置服务名称
        ExcelOperationUtil.setCellForIndex(operaRow,1,acsmapList.get(0).get(5).toString());
//        设置提供方组件名称
        ExcelOperationUtil.setCellForIndex(operaRow,2,"个人存款组件");
//        设置服务消费方系统
        ExcelOperationUtil.setCellForIndex(operaRow,7,String.join("|",systemList));

//        设置超链接及提供方服务标识
        CreationHelper createHelper = new XSSFCreationHelper((XSSFWorkbook) workbook);
        Hyperlink  link1 = createHelper.createHyperlink(HyperlinkType.DOCUMENT);

        link1.setAddress("#"+acsmapList.get(0).get(3).toString()+"!A1");
        operaCell = operaRow.getCell(6);
        if (operaCell == null){
            operaCell = operaRow.createCell(6);
        }
        operaCell.setHyperlink(link1);
        operaCell.setCellValue(acsmapList.get(0).get(3).toString());

        operaSheet = this.workbook.cloneSheet(7);
        int operaInt = this.workbook.getSheetIndex(operaSheet);
        String sheetname = acsmapList.get(0).get(3).toString();
        this.workbook.setSheetName(operaInt,sheetname);
//        List<CellRangeAddress> originMerged = operaSheet.getMergedRegions();



        ExcelOperationUtil.setCellForCoordinate(operaSheet,1,0,acsmapList.get(0).get(5).toString());
        ExcelOperationUtil.setCellForCoordinate(operaSheet,5,3,acsmapList.get(1).get(1).toString());
        ExcelOperationUtil.setCellForCoordinate(operaSheet,6,3,"个人存款组件");
        ExcelOperationUtil.setCellForCoordinate(operaSheet,18,3,acsmapListQT.get(2).get(5).toString());

        operaRow = operaSheet.getRow(19);
        operaCell = operaRow.getCell(3);
        operaCell.setCellValue(String.join("|",systemList));
        ExcelOperationUtil.setCellForCoordinate(operaSheet,19,3,String.join("|",systemList));
        ExcelOperationUtil.setCellForCoordinate(operaSheet,20,3,"否");


        List<List<String>> inListList = getAreaListList(acsmapList,integerList,4,5);
        List<List<String>> inListListConvert = convertAreaListList(inListList);
//        int addnum = inListList.size() - 10 + 1;
//        System.out.println(addnum);
//        operaSheet.shiftRows( 30, operaSheet.getLastRowNum()+1, addnum, true, false);

        int l1 = intOutAreaWrite(operaSheet,29,inListList.size(),0,inListListConvert);

        List<List<String>> outListList = getAreaListList(acsmapList,integerList,5,6);
        List<List<String>> outListListConvert = convertAreaListList(outListList);

        int l2 = intOutAreaWrite(operaSheet,43,outListList.size(),l1,outListListConvert);

//        int l2 = 0;
//        for (int i = 43+l1; i < 43+l1+outListList.size(); i++) {
//            if(i > 43+2+l1){
//                operaSheet.shiftRows( i, operaSheet.getLastRowNum()+1, 1, true, false);
//                l2++;
//
//                operaRow = operaSheet.createRow(i);
//                operaRow.setRowStyle(operaSheet.getRow(i-1).getRowStyle());
//            }else {
//                operaRow = operaSheet.getRow(i);
//            }
//            operaRow.setHeight(operaSheet.getRow(30).getHeight());
//            for (int j = 1; j < 10; j++) {
//                operaCell = operaRow.getCell(j);
//                if(operaCell == null){
//                    operaCell = operaRow.createCell(j);
//                    operaCell.setCellStyle(operaSheet.getRow(i-1).getCell(j).getCellStyle());
////                    System.out.println(operaCell.getStringCellValue());
//                }
//                String tempString = null;
//                if(outListListConvert.get(i-(43+l1)).get(j-1) != null){
//                    tempString = outListListConvert.get(i-43-l1).get(j-1).toString();
//                    operaCell.setCellValue(tempString);
//                }else{
//                    continue;
//                }
//            }
//        }

    }

    public int intOutAreaWrite(Sheet operaSheet,int flagNum ,int overNum,int anyNum,List<List<String>> inListListConvert){
        Row operaRow = null;
        Cell operaCell = null;
        int l1 = 0;
//        flagNum = 29|43
        for (int i = flagNum+anyNum; i <flagNum+anyNum+overNum; i++) {
            if(i > flagNum+3+anyNum){

                operaSheet.shiftRows( i, operaSheet.getLastRowNum()+1, 1, true, false);
                l1++;

                operaRow = operaSheet.createRow(i);
                operaRow.setRowStyle(operaSheet.getRow(30).getRowStyle());
            }else {
                operaRow = operaSheet.getRow(i);
            }

            operaRow.setHeight(operaSheet.getRow(i-1).getHeight());
            for (int j = 1; j < 10; j++) {
                operaCell = operaRow.getCell(j);
                if(operaCell == null){
                    operaCell = operaRow.createCell(j);
                    operaCell.setCellStyle(operaSheet.getRow(i-1).getCell(j).getCellStyle());
//                    System.out.println(operaCell.getStringCellValue());
                }
                String tempString = null;
                if(inListListConvert.get(i-flagNum-anyNum).get(j-1) != null){
                    tempString = inListListConvert.get(i-flagNum-anyNum).get(j-1).toString();
                    operaCell.setCellValue(tempString);
                }else{
                    continue;
                }
            }

        }
        return  l1 ;

    }

    private List<List<String>> getAreaListList(List<Map<Integer, Object>> acsmapList, List<Integer> integerList, int i, int i1) {
        int j = integerList.get(i);
        int k  = integerList.get(i1);

        List<List<String>> stringListList = new ArrayList<>();
        for (int l = j+1; l < k; l++) {
            List<String> stringList = new ArrayList<String>();
            for (int m = 0; m < acsmapList.get(l).size(); m++) {
                stringList.add((String)acsmapList.get(l).get(m));
            }
            stringListList.add(stringList);
        }

        return stringListList;


    }

    public List<List<String>> convertAreaListList(List<List<String>> inListList){
        Map<Integer,String> converMap = new HashMap<>();
        converMap.put(1,"Y_1");
        converMap.put(2,"D_4");
        converMap.put(3,"Y_3");
        converMap.put(4,"D_1");
        converMap.put(5,"D_2");
        converMap.put(6,"D_3");
        converMap.put(7,"Y_4");
        converMap.put(8,"Y_8");
        converMap.put(9,"Y_9");

        List<List<String>> lists = new ArrayList<>();

        for (int i = 0; i <inListList.size(); i++) {
            List<String> tempList = new ArrayList<>();
            List<String> sysDictList = getsysDict(inListList.get(i).get(3));
            for (int j = 1; j < 10; j++) {
                String[] converMapAarry = converMap.get(j).split("_");
                if(converMapAarry[0].equals("Y")){
                    int k = Integer.valueOf(converMapAarry[1]);
                    if(j<inListList.get(i).size()){
                        tempList.add(inListList.get(i).get(k));
                    }else{
                        tempList.add(null);
                    }
                }else if (converMapAarry[0].equals("D")){
                    int k = Integer.valueOf(converMapAarry[1])-1;
                    if(k<sysDictList.size()){
                        tempList.add(sysDictList.get(k));
                    }else {
                        tempList.add(null);
                    }
                }
//                if(j < 4 || j > 7){
//                    if(j<inListList.get(i).size()){
//                        tempList.add(inListList.get(i).get(j));
//                    }else{
//                        tempList.add(null);
//                    }
//                }
//                if(j == 4 || j == 5 || j == 6){
//                    tempList.add(sysDictList.get(j-4));
//                }
//                if(j == 7){
//                    tempList.add(inListList.get(i).get(j-3));
//                }



            }
            lists.add(tempList);
        }

        return lists;
    }

    private List<String> getAreaList(List<Map<Integer, Object>> acsmapList, List<Integer> integerList, int i, int i1,int i2) {

        int j = integerList.get(i);
        int k  = integerList.get(i1);

        Set<String> stringSet = new HashSet<>();
        List<String> stringList = new ArrayList<>();
        for (int l = j+1; l < k; l++) {
            Object syscodeString = acsmapList.get(l).get(i2);
            if(syscodeString!=null && syscodeString.toString().length()>0){
                stringList.add((String)syscodeString);
            }else {
                LOGGER.info("未正确获取第{}个渠道数据",l);
            }
        }

        stringSet.addAll(stringList);
        List<String> resultList = new ArrayList<>();
        resultList.addAll(stringSet);

        return resultList;



    }

    public List<String> getsysDict(String name){

//        sysDictmapList
        List<String> sysDictList = new ArrayList<>();
        for (int i = 0; i < sysDictmapList.size(); i++) {
            if(sysDictmapList.get(i).get(4).equals(name)){
                sysDictList.add(sysDictmapList.get(i).get(0).toString());
                sysDictList.add(sysDictmapList.get(i).get(17).toString());
                sysDictList.add(sysDictmapList.get(i).get(15).toString());
                sysDictList.add(sysDictmapList.get(i).get(19).toString());

                break;
            }
        }
        return sysDictList;
    }


    /*
    执行写入excel文件任务
     */
    public void writeExcel(){
        try {
            ExcelOperationUtil.writeWorkbookToFile(this.workbook,this.filedir+File.separator+this.resultFileName);
        } catch (IOException e) {
            e.printStackTrace();
            LOGGER.info(e.getStackTrace().toString());

        }
    }
}
